package com.hwadee.mapper;

import com.hwadee.pojo.Doctor;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface DoctorMapper {
    //查询所有医生及其对应绑定的老人数
    @Select("SELECT " +
            "d.id, d.name, d.work_id AS workId, d.department, d.role_group AS roleGroup, " +
            "COUNT(o.id) AS elderlyCount " +
            "FROM doctor d " +
            "LEFT JOIN oldinfo o ON d.id = o.doctor_id " +//使用 LEFT JOIN 保证即使某医生没有绑定老人也能查询到
            "GROUP BY d.id, d.name, d.work_id, d.department, d.role_group")//GROUP BY 实现按医生分组计数
    List<Doctor> getAllDoctorsWithElderlyCount();
    //根据医生 ID 查询医生基本信息
    @Select("SELECT id, name, work_id AS workId, department, role_group AS roleGroup FROM doctor WHERE id = #{id}")
    Doctor getDoctorById(Integer id);
    //查询某位医生所绑定的所有老年人姓名
    @Select("SELECT name FROM oldinfo WHERE doctor_id = #{doctorId}")
    List<String> getBoundElderNames(Integer doctorId);
    //根据医生姓名查找对应的医生 ID
    @Select("SELECT id FROM doctor WHERE name = #{name}")
    Integer findIdByName(String name);
}
